﻿Imports System.Data.SqlClient
Imports System.IO

Public Class frmProspectsCasesNew

    Dim FolderPath As String = FTPAddress + "Prospects Cases"

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSave.Click
        Me.ErrProv.Clear()
        If txtProspName.Text.Length = 0 Then
            Me.ErrProv.SetError(txtProspName, "Please fill in all required fields")
            Me.txtProspName.Focus()

        ElseIf txtBusinessSize.Text.Length = 0 Then
            Me.ErrProv.SetError(txtBusinessSize, "Please fill in all required fields")
            Me.txtBusinessSize.Focus()

        Else
            Try
                Me.Cursor = Cursors.WaitCursor

                Dim cmd As New SqlCommand()
                Dim Trans As SqlTransaction
                Dim ProsNo, Year, SNo As Integer
                Dim ProsType, ClassOfBusiness As String

                'Check Prospect type
                If Me.RGeneral.Checked = True Then
                    ProsType = "GEN"
                    ClassOfBusiness = "General"

                ElseIf Me.RMedical.Checked = True Then
                    ProsType = "MED"
                    ClassOfBusiness = "Medical"

                ElseIf Me.RLife.Checked = True Then
                    ProsType = "LIF"
                    ClassOfBusiness = "Life"

                ElseIf RMotor.Checked = True Then
                    ProsType = "MOT"
                    ClassOfBusiness = "Motor"
                End If

                cnn.Open()
                Trans = cnn.BeginTransaction
                cmd.Connection = cnn
                cmd.Transaction = Trans

                'Get prosp. case no. based on (Class, Year)
                cmd.CommandText = "Select IsNull(Max(PNo),0) From ProspectCases Where Year(TransDate)=Year(GetDate()) and ProsType=N'" & ProsType & "'"
                ProsNo = CInt(cmd.ExecuteScalar) + 1

                cmd.CommandText = "Select Year(GetDate())"
                Year = CInt(cmd.ExecuteScalar)

                cmd.CommandText = "Insert Into ProspectCases (Branch,PNo,ProsNo,ProsType,ProspectID,ProspectName,Status,TargetRate,Notes,InsuranceCompany," & _
                                  "InsuranceBroker,ClassofBusiness,BusinessSize,ExpiryDate,AssignedTo,AssignedBy,AssignedDate,TransDate,SavedUser) Values " & _
                                  "(@Branch,@PNo,@ProsNo,@ProsType,@ProspectID,@ProspectName,@Status,@TargetRate,@Notes," & _
                                  "@InsuranceCompany,@InsuranceBroker,@ClassofBusiness,@BusinessSize,@ExpiryDate,@AssignedTo,@AssignedBy,GetDate(),GetDate(),@SavedUser) " & _
                                  "Select SCOPE_IDENTITY()"

                'Add values
                cmd.Parameters.AddWithValue("@Branch", Branch)
                cmd.Parameters.AddWithValue("@PNo", ProsNo)
                cmd.Parameters.AddWithValue("@ProsNo", "PC-" & BranchCode & "-" & Year & "-" & ProsType & "-" & ProsNo)
                cmd.Parameters.AddWithValue("@ProsType", ProsType)
                cmd.Parameters.AddWithValue("@ProspectID", Me.txtProspID.Text.Trim)
                cmd.Parameters.AddWithValue("@ProspectName", Me.txtProspName.Text.Trim)
                cmd.Parameters.AddWithValue("@Status", "Active")
                cmd.Parameters.AddWithValue("@TargetRate", Me.txtTargetRate.Text)
                cmd.Parameters.AddWithValue("@Notes", Me.txtNotes.Text)
                cmd.Parameters.AddWithValue("@InsuranceCompany", Me.txtInsurComp.Text.Trim)
                cmd.Parameters.AddWithValue("@InsuranceBroker", Me.txtInsurBroker.Text.Trim)
                cmd.Parameters.AddWithValue("@ClassofBusiness", ClassOfBusiness)
                cmd.Parameters.AddWithValue("@BusinessSize", Me.txtBusinessSize.Text.Trim)

                'Check if there is an existing policy that will expire
                If Me.ChExpiryDate.Checked = True Then
                    cmd.Parameters.AddWithValue("@ExpiryDate", Me.DTPExpiryDate.Value.ToShortDateString & " 10:10:10 AM")
                Else
                    cmd.Parameters.AddWithValue("@ExpiryDate", DBNull.Value)
                End If
                cmd.Parameters.AddWithValue("@AssignedTo", CurrentUser)
                cmd.Parameters.AddWithValue("@AssignedBy", CurrentUser)
                cmd.Parameters.AddWithValue("@SavedUser", CurrentUser)

                SNo = CInt(cmd.ExecuteScalar())

                'Add Note to Know Start Quotation Date
                cmd.CommandText = "Insert Into ProspectsNotes (ProsNo,Notes,SavedUser) Values " & _
                                  "(@ProsNo,@Notes,@SavedUser)"

                'Add Values
                cmd.Parameters.Clear()
                cmd.Parameters.AddWithValue("@ProsNo", SNo)
                cmd.Parameters.AddWithValue("@Notes", "Prospect case registered")
                cmd.Parameters.AddWithValue("@SavedUser", CurrentUser)
                cmd.ExecuteNonQuery()

                Trans.Commit()
                cnn.Close()

                'Create directory to store files
                Dim Dir As New DirectoryInfo(FolderPath)
                Dir.CreateSubdirectory("PC-" & BranchCode & "-" & Year & "-" & ProsType & "-" & ProsNo)

                MsgBox("Saved Successfully", MsgBoxStyle.Information, Me.Text)

                'Clear
                Me.txtInsurComp.Clear()
                Me.txtInsurBroker.Clear()
                Me.ChExpiryDate.Checked = False
                Me.txtBusinessSize.Clear()
                Me.txtInsurComp.Focus()
                Me.txtNotes.Clear()
                Me.txtTargetRate.Clear()

                Me.Cursor = Cursors.Default
            Catch ex As Exception
                Me.Cursor = Cursors.Default
                If cnn.State = ConnectionState.Open Then
                    cnn.Close()
                End If
                MsgBox(ex.ToString)
            End Try
        End If
    End Sub

    Sub Clear()
        Me.txtProspID.Clear()
        Me.txtProspName.Clear()
        Me.txtNotes.Clear()
        Me.txtTargetRate.Clear()
        Me.txtInsurComp.Text = ""
        Me.txtInsurBroker.Text = ""
        Me.ChExpiryDate.Checked = False
        Me.txtBusinessSize.Clear()
        Me.txtNotes.Clear()
        Me.txtTargetRate.Clear()
        Me.txtProspID.Focus()
    End Sub

    Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSearchID.Click
        Dim a As New frmSearchProspect
        a.ShowDialog()

        If SelProspectID = 0 Then
            Exit Sub
        End If

        Me.txtProspID.Text = SelProspectID
        Me.txtProspName.Text = SelProspectName
    End Sub

    Private Sub btnClose_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnClose.Click
        Me.Close()
    End Sub

    Private Sub txtProspectID_KeyUp(ByVal sender As Object, ByVal e As System.Windows.Forms.KeyEventArgs) Handles txtProspID.KeyUp
        If e.KeyCode = Keys.Enter Then
            If Me.txtProspID.Text.Trim.Length > 0 Then
                Me.txtProspName.Text = GetProspectName(CInt(Me.txtProspID.Text.Trim))
            End If
        End If
    End Sub

    Private Sub txtProspID_TextChanged(sender As System.Object, e As System.EventArgs) Handles txtProspID.TextChanged
        Me.txtProspName.Clear()
    End Sub

    Private Sub ChExpiryDate_CheckedChanged(sender As System.Object, e As System.EventArgs) Handles ChExpiryDate.CheckedChanged
        If Me.ChExpiryDate.Checked = True Then
            Me.DTPExpiryDate.Enabled = True
            Me.DTPExpiryDate.Value = Today.Date

        Else
            Me.DTPExpiryDate.Enabled = False
            Me.DTPExpiryDate.Value = Today.Date
        End If
    End Sub

    Private Sub btnClear_Click(sender As System.Object, e As System.EventArgs) Handles btnClear.Click
        Clear()
    End Sub
End Class